package com.jqq.api.preparestatement;

import org.junit.jupiter.api.Test;

import java.sql.*;

/**
 * @author 姬倩倩
 * @version 1.0
 */
public class PSOtherPart {
    /**
     * TODO:
     *      t_user插入一条数据，并且获取数据库自增长的主键
     * TODO:
     *  1.要在创建preparedStatement时，告知，携带回数据库自增长的主键(sql, Statement.RETURN_GENERATED_KEYS)
     *  2.获取司机装主键值的结果集对象，一行一列，获取对应的数据即可 ResultSet resultSet = statement.getGeneratedKeys();
     */
    @Test
    public void returnPrimaryKey() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test_q?user=root&password=123456");
        //3.编写SQL语句
        String sql = "insert into t_user(account, password, nickname) values(?,?,?);";

        //4.创建statement
        PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        //5.占位符赋值
        statement.setObject(1, "test1");
        statement.setObject(2, "123456");
        statement.setObject(3, "大黄");

        //6.发送SQL语句，并且获取结果
        int i = statement.executeUpdate();

        //7.结果解析
        if(i > 0){
            System.out.println("数据插入成功！");

            //可以获取回显的主键
            //获取司机装主键的结果集对象 一行一列 id=值
            ResultSet resultSet = statement.getGeneratedKeys();
            resultSet.next();//移动下光标，让光标指向当前数据
            int id = resultSet.getInt(1);
            System.out.println("id = "+ id);
        } else{
            System.out.println("数据插入失败！");
        }

        //8.关闭资源
        statement.close();
        connection.close();
    }

    @Test
    /**
     * 使用普通的方式，插入10000条数据
     */
    public void testInsert() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test_q?user=root&password=123456");
        //3.编写SQL语句
        String sql = "insert into t_user(account, password, nickname) values(?,?,?);";

        //4.创建statement
        PreparedStatement statement = connection.prepareStatement(sql);

        //5.占位符赋值
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            statement.setObject(1, "qq"+i);
            statement.setObject(2, "qq"+i);
            statement.setObject(3, "七七"+i);

            //6.发送SQL语句，并且获取结果
            statement.executeUpdate();
        }
        long end = System.currentTimeMillis();

        //7.结果解析
        System.out.println("执行10000次消耗:"+(end-start));//执行10000次消耗:10673
        //8.关闭资源
        statement.close();
        connection.close();
    }

    @Test
    /**
     * 使用批量插入的方式，插入10000条数据
     * TODO:
     *  1.路径后边添加 ?rewriteBatchedStatements=true 允许批量插入
     *  2.insert into values[必须后缀s] 不能加;结束
     *  3.不是执行每条语句，是批量添加 addBatch();
     *  4.遍历添加完毕后，统一批量执行 executeBatch();
     */
    public void testBatchInsert() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql:///test_q?rewriteBatchedStatements=true","root","123456");
        //3.编写SQL语句
        String sql = "insert into t_user(account, password, nickname) values(?,?,?)";

        //4.创建statement
        PreparedStatement statement = connection.prepareStatement(sql);

        //5.占位符赋值
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            statement.setObject(1, "qqq"+i);
            statement.setObject(2, "qqq"+i);
            statement.setObject(3, "七七q"+i);

            //不执行，追加到values后边
            statement.addBatch();
        }
        //统一提交
        statement.executeBatch();//执行批量操作
        long end = System.currentTimeMillis();

        //7.结果解析
        System.out.println("执行10000次消耗:"+(end-start));//执行10000次消耗:221
        //8.关闭资源
        statement.close();
        connection.close();
    }
}
